﻿namespace Access
{
    using ConfigDatabase.Class;
    using Npgsql;
    using System;
    using System.Data;
    using System.Data.OleDb;

    public class General
    {
        private ConnectDatabase _condata;
        private string _schema;

        public General()
        {
            this._schema = "";
            this._condata = new ConnectDatabase();
            this._schema = this._condata.Schema;
        }

        public General(ConnectDatabase condata)
        {
            this._schema = "";
            this._condata = condata;
            this._schema = this._condata.Schema;
        }

        public string f_Get_CapID()
        {
            try
            {
                long num = 0L;
                num = long.Parse(this.f_Get_dulieu("select to_char(now(),'yymmddhh24missms') from  " + this._condata.Schema + ".dsthongso limit 1").Tables[0].Rows[0][0].ToString());
                string str = this.f_get_sequences();
                num += Convert.ToInt64(str.Substring(0, 2));
                return (num.ToString() + str.Substring(2));
            }
            catch
            {
                return "1";
            }
        }

        public string f_Get_CapID(string tenTable, string tenfield)
        {
            try
            {
                long num = 0L;
                num = long.Parse(this.f_Get_dulieu("select max(cast(" + tenfield + " as int8 )) as a from " + this._condata.Schema + "." + tenTable).Tables[0].Rows[0][0].ToString()) + 1L;
                return num.ToString();
            }
            catch
            {
                return "1";
            }
        }

        public DataSet f_Get_dulieu(string sql)
        {
            DataSet dataSet = new DataSet();
            NpgsqlConnection selectConnection = new NpgsqlConnection(this._condata.StringConnect);
            selectConnection.Open();
            try
            {
                new NpgsqlDataAdapter(sql, selectConnection).Fill(dataSet);
            }
            catch (Exception exception)
            {
                ConnectDatabase.TrackingError(exception.ToString(), "f_Get_dulieu()", "access");
                return null;
            }
            finally
            {
                selectConnection.Close();
            }
            return dataSet;
        }

        private string f_get_sequences()
        {
            DataSet set = new DataSet();
            long num = 0L;
            try
            {
                set = this.f_loadfile_xmlsequences();
                num = Convert.ToInt64(set.Tables[0].Rows[0]["stt"].ToString()) + 1L;
                set.Tables[0].Clear();
                DataRow row = set.Tables[0].NewRow();
                row["stt"] = num.ToString();
                row["ngay"] = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
                set.Tables[0].Rows.InsertAt(row, 0);
                set.WriteXml("xmlsequences.xml", XmlWriteMode.WriteSchema);
            }
            catch
            {
            }
            return num.ToString().PadLeft(5, '0');
        }

        public DataTable f_getdata_fromfileExcel(string filePath, string sheetname)
        {
            DataSet dataSet = new DataSet();
            string[] strArray = filePath.Split(new char[] { '.' });
            string connectionString = "";
            if ((filePath.Length > 1) && (strArray[1] == "xls"))
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;";
            }
            else
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
            }
            OleDbConnection selectConnection = new OleDbConnection(connectionString);
            try
            {
                selectConnection.Open();
                new OleDbDataAdapter("select * from [" + sheetname + "$]", selectConnection).Fill(dataSet);
            }
            catch
            {
                return null;
            }
            finally
            {
                selectConnection.Close();
            }
            if (dataSet != null)
            {
                for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
                {
                    if (dataSet.Tables[0].Columns[i].ColumnName.Substring(0, 1) == "F")
                    {
                        dataSet.Tables[0].Columns.RemoveAt(i--);
                    }
                }
            }
            return dataSet.Tables[0];
        }

        private DataSet f_loadfile_xmlsequences()
        {
            DataSet set = new DataSet();
            try
            {
                set.ReadXml("xmlsequences.xml", XmlReadMode.ReadSchema);
                string str = set.Tables[0].Rows[0]["stt"].ToString();
                string str2 = set.Tables[0].Rows[0]["ngay"].ToString().Substring(0, 10);
            }
            catch
            {
                set = new DataSet();
                set.Tables.Add();
                set.Tables[0].Columns.Add("stt");
                set.Tables[0].Columns.Add("ngay");
            }
            for (int i = 0; i < set.Tables[0].Rows.Count; i++)
            {
                if (set.Tables[0].Rows[i]["ngay"].ToString().Substring(0, 10) == DateTime.Now.ToString("dd/MM/yyyy"))
                {
                    break;
                }
                set.Tables[0].Rows.RemoveAt(i--);
            }
            if (set.Tables[0].Rows.Count == 0)
            {
                DataRow row = set.Tables[0].NewRow();
                row["stt"] = 0;
                row["ngay"] = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
                set.Tables[0].Rows.Add(row);
            }
            set.WriteXml("xmlsequences.xml", XmlWriteMode.WriteSchema);
            return set;
        }

        public int f_Update_dulieu(string sql)
        {
            int num;
            NpgsqlConnection connect = new NpgsqlConnection(this._condata.StringConnect);
            connect.Open();
            try
            {
                num = this.f_Update_dulieu(sql, connect);
            }
            catch (Exception exception)
            {
                ConnectDatabase.TrackingError(exception.ToString(), "f_Get_dulieu()", "access");
                num = -1;
            }
            finally
            {
                connect.Close();
            }
            return num;
        }

        public int f_Update_dulieu(string sql, NpgsqlConnection connect)
        {
            int num = -1;
            try
            {
                num = new NpgsqlCommand(sql, connect).ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                ConnectDatabase.TrackingError(exception.ToString(), "f_Update_dulieu()", "access");
                return -1;
            }
            return num;
        }

        public ConnectDatabase pConFigData
        {
            get
            {
                return this._condata;
            }
        }

        public string pSchema
        {
            get
            {
                return this._schema;
            }
        }
    }
}

